<?php
/*
 * @Author: your name
 * @Date: 2021-11-02 19:06:50
 * @LastEditTime: 2022-03-06 23:02:35
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: /nlog/app/Models/admin_model/Admin.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Admins extends Model
{
    use HasFactory;
    static string $tableName = 'admins';

    public static function login($userName, $passWord)
    {
        return DB::table(self::$tableName)->where(['userName' => $userName, 'passWord' => $passWord, 'delete_time' => null])->first();
    }

    public static function checkUserName($userName){
        return Db::table(self::$tableName)->where(['userName'=>$userName,'delete_time'=>null])->select('id')->first();
    }

    public static function list($whereStr = '', $limitStr = '')
    { 
        // echo ' SELECT  `admins`.*,`role`.`name` as `roleName`,CASE WHEN `admins`.`division_id` = 0  THEN "全公司"  ELSE `crm_division`.`name` as `division_name`   FROM ' . self::$tableName . ' LEFT JOIN `role` ON `role`.`id` = `admins`.`roleId`  LEFT JOIN `crm_division` ON `crm_division`.`id` = `admins`.`division_id`    WHERE  `admins`.`roleId` != 0 AND `admins`.`delete_time` is null ' . $whereStr . ' ORDER BY `admins`.`top_time` DESC,`admins`.`id` DESC  ' . $limitStr;
        // echo ' SELECT  `admins`.*,`role`.`name` as `roleName`,CASE WHEN `admins`.`division_id` = 0  THEN "全公司"  ELSE `crm_division`.`name` END as `division_name`   FROM ' . self::$tableName . ' LEFT JOIN `role` ON `role`.`id` = `admins`.`roleId`  LEFT JOIN `crm_division` ON `crm_division`.`id` = `admins`.`division_id`    WHERE  `admins`.`roleId` != 0 AND `admins`.`delete_time` is null ' . $whereStr . ' ORDER BY `admins`.`top_time` DESC,`admins`.`id` DESC  ' . $limitStr;
    //    echo ' SELECT  `admins`.*,`role`.`name` as `roleName`,CASE WHEN `admins`.`division_id` = 0  THEN "全公司"  ELSE `crm_division`.`name` END as `division_name`   FROM ' . self::$tableName . ' LEFT JOIN `role` ON `role`.`id` = `admins`.`roleId`  LEFT JOIN `crm_division` ON `crm_division`.`id` = `admins`.`division_id`    WHERE  `admins`.`roleId` != 0 AND `admins`.`delete_time` is null ' . $whereStr . ' ORDER BY `admins`.`top_time` DESC,`admins`.`id` DESC  ' . $limitStr;
        return DB::select(' SELECT  `admins`.*,CASE WHEN `admins`.`roleId` = -1 THEN "无权限"  ELSE  `role`.`name` END as `roleName`, `crm_division`.`name`  as `division_name`   FROM ' . self::$tableName . ' LEFT JOIN `role` ON `role`.`id` = `admins`.`roleId`  LEFT JOIN `crm_division` ON `crm_division`.`id` = `admins`.`division_id`    WHERE  `admins`.`roleId` != 0 AND `admins`.`delete_time` is null ' . $whereStr . ' ORDER BY `admins`.`top_time` DESC,`admins`.`id` DESC  ' . $limitStr);
    }
    

    public static function count($whereStr = '')
    {
        return DB::select(' SELECT COUNT(*) as `count` FROM ' . self::$tableName . '  LEFT JOIN `crm_division` ON `crm_division`.`id` = `admins`.`division_id`  WHERE `admins`.`roleId` != 0 AND `admins`.`delete_time` is null  ' . $whereStr);
    }

    public static function selectList(){
        
        return DB::table(self::$tableName)->where(['status'=>1,'delete_time'=>null])->orderBy('top_time', 'desc')->orderBy('id', 'desc')->get(['id','nickName','realName','phone']);
    }

    public static function expertsList(){
        return DB::select('  SELECT COUNT(*) as `fixtureNumber`,`admins`.`id`,`admins`.`nickName`,`admins`.`realName`,`admins`.`avatar` FROM `admins` RIGHT  JOIN (SELECT `admin_id` FROM `store` WHERE `shelves` = 1 AND `delete_time` is null  union all SELECT `admin_id` FROM `housing` WHERE `shelves` = 1 AND `delete_time` is null ) as `all` ON `all`.`admin_id` = `admins`.`id` WHERE `admins`.`delete_time` is null AND `admins`.`status` = 1 GROUP BY `admin_id` ');
    }

    public static function expertsSelOne($id){
        return DB::select('  SELECT COUNT(*) as `fixtureNumber`,`admins`.`phone`,`admins`.`id`,`admins`.`nickName`,`admins`.`realName`,`admins`.`avatar` FROM `admins` RIGHT  JOIN (SELECT `admin_id` FROM `store` WHERE `shelves` = 1 AND `delete_time` is null  union all SELECT `admin_id` FROM `housing` WHERE `shelves` = 1 AND `delete_time` is null ) as `all` ON `all`.`admin_id` = `admins`.`id` WHERE `admins`.`id` = '.$id.' GROUP BY `admin_id` LIMIT 1 ');
    }

    public static function selList($whereStr, $limitStr){
        // echo ' SELECT  * FROM '.self::$tableName.' WHERE  `delete_time` is null '.$whereStr.' ORDER BY `top_time` DESC  , `id` DESC  '.$limitStr;
        return DB::select(' SELECT  * FROM '.self::$tableName.' WHERE  `delete_time` is null '.$whereStr.' ORDER BY `top_time` DESC  , `id` DESC  '.$limitStr);
    }
    //         
       

}
